package cz.acies.genea.data;

import java.io.File;
import java.sql.Connection;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.io.FileUtils;

import cz.acies.data.DBConnector;
import cz.acies.data.DBUpadaterAsbtract;
import cz.acies.data.dao.DefaultDAO;
import cz.acies.data.dao.DefaultMapListHandler;
import cz.acies.data.dao.DefaultRowProcessor;
import cz.acies.data.dao.UserPersonDAO;
import cz.acies.data.persist.ModelObject;
import cz.acies.data.persist.UserPerson;
import cz.acies.uniql.metadata.DBMetaData;
import cz.acies.uniql.sql.InsertQuery;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLJoin;
import cz.acies.uniql.sql.functions.SQLFunc;

public class DBUpdater extends DBUpadaterAsbtract
{
	public DBUpdater() {
		super();
	}

	public DBUpdater(Connection conn, DBMetaData dbmd) {
		super(conn, dbmd);
	}

	@Override
	public void updateStructure() throws Exception {
		//System.out.println("DBUpdater.updateStructure()");
		String table = "";
		
		table = "mail_address";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "mailAddress", Types.VARCHAR, 128, 0, false, "", true, "", conn);
		}

		table = "project";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "userId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "name", Types.VARCHAR, 128, 0, false, "", true, "", conn);
		}

		table = "person";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "projectId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "firstName", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "lastName", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "maidenName", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "title", Types.VARCHAR, 20, 0, false, "", false, "", conn);
			dbmd.updateColumn(table, "sufix", Types.VARCHAR, 20, 0, false, "", false, "", conn);
			dbmd.updateColumn(table, "gender", Types.VARCHAR, 20, 0, false, "M", false, "", conn);
			dbmd.updateColumn(table, "birthDate", Types.DATE, 10, 0, false, "2099-01-01", true, "", conn);
			dbmd.updateColumn(table, "birthPlace", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "isDeath", Types.TINYINT, 4, 0, false, "0", true, "", conn);
			dbmd.updateColumn(table, "deathDate", Types.DATE, 10, 0, false, "2099-01-01", true, "", conn);
			dbmd.updateColumn(table, "deathPlace", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "parents", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}

		table = "family";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "projectId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "manId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "womanId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}
		
		table = "event_fact";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "type", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "date", Types.TIMESTAMP, 10, 0, false, "2099-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "place", Types.VARCHAR, 128, 0, false, "", false, "", conn);
			dbmd.updateColumn(table, "descript", Types.VARCHAR, 4096, 0, false, "", true, "", conn);
		}
		/*
		table = "children";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "childId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}
		*/
		table = "person_bindings";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "objectId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "connectId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "bindingType", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "extend", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}

		table = "geo_point";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "longitude", Types.DOUBLE, 15, 8, false, "0.0", true, "", conn);
			dbmd.updateColumn(table, "latitude", Types.DOUBLE, 15, 8, false, "0.0", true, "", conn);
			dbmd.updateColumn(table, "altitude", Types.DOUBLE, 15, 8, false, "0.0", true, "", conn);
			dbmd.updateColumn(table, "descript", Types.VARCHAR, 4096, 0, false, "", false, "", conn);
		}

		table = "postal_address";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "city", Types.VARCHAR, 64, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "street", Types.VARCHAR, 64, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "region", Types.VARCHAR, 64, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "postCode", Types.VARCHAR, 64, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "country", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}

		table = "register_book";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "registerOffice", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "bookCode", Types.VARCHAR, 32, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "website", Types.VARCHAR, 256, 0, false, "", false, "", conn);
			dbmd.updateColumn(table, "descript", Types.VARCHAR, 4096, 0, false, "", false, "", conn);
		}

		table = "register_office";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "name", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "address", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			
			//dbmd.renameColumn(table, "address", "addressId", conn);
		}
		
		//ALTER TABLE xyz CHANGE manufacurerid manufacturerid
		
		// -----  -------------------------------------------------------------
		
		table = "user_repository";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "CHANGE", conn);
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "userId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "label", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "image", Types.VARCHAR, 128, 0, false, "", false, "", conn);
			dbmd.updateColumn(table, "baseType", Types.VARCHAR, 20, 0, false, "/", true, "", conn);
			dbmd.updateColumn(table, "basePath", Types.VARCHAR, 255, 0, false, "/", true, "", conn);
			//dbmd.updateColumn(table, "path", Types.INTEGER, 11, 0, false, "0", true, "", conn);
		}
		table = "user_repository_record";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "CHANGE", conn);
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "fileName", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "fileExt", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}
		
	}

	@Override
	public void updateData() throws Exception {
		String basePath = (String) sCtx().getInitParameter("directoryFileStore");
		File baseDir = new File(basePath);
		basePath = baseDir.getPath().replaceAll("\\\\", "/");
		//System.out.println("basePath: "+basePath);
		if (!baseDir.exists()) {
			baseDir.mkdirs();
		}
		DefaultDAO dao = new DefaultDAO();
		List<ModelObject> data = dao.selectById("user_repository", "UR.F.00000000000ROOT", ModelObject.class);
		if (data.isEmpty()) {
			InsertQuery isql = new InsertQuery(dbmd())
			.into("user_repository")
			.addParam("id", "UR.F.00000000000ROOT")
			.addParam("parentId", "")
			.addParam("userId", "")
			.addParam("label", "Soubory")
			.addParam("image", "")
			.addParam("baseType", "NT.X.UDS00000000ROOT")
			.addParam("basePath", "")
			.addParam("writerId", "WX.L.0000000000ADMIN")
			.addParam("writeDate", new SQLFunc("NOW"))
			.addParam("editorId", "WX.L.0000000000ADMIN")
			.addParam("editDate", new SQLFunc("NOW"));
			
			qr.update(isql.toSQLString(true));
		}
		data = dao.selectById("user_repository", "UR.F.00000000BASEDIR", ModelObject.class);
		if (data.isEmpty()) {
			InsertQuery isql = new InsertQuery(dbmd())
			.into("user_repository")
			.addParam("id", "UR.F.00000000BASEDIR")
			.addParam("parentId", "UR.F.00000000000ROOT")
			.addParam("userId", "")
			.addParam("label", "Dokumenty")
			.addParam("image", "")
			.addParam("baseType", "NT.X.UDS000000000URL")
			.addParam("basePath", ""+basePath)
			.addParam("writerId", "WX.L.0000000000ADMIN")
			.addParam("writeDate", new SQLFunc("NOW"))
			.addParam("editorId", "WX.L.0000000000ADMIN")
			.addParam("editDate", new SQLFunc("NOW"));
			
			qr.update(isql.toSQLString(true));			
		}
		/*
		Properties props = System.getProperties();
		for (String key : props.keySet().toArray(new String[0])) {
			System.out.println(key + " = " + props.getProperty(key)); 
		}
		*/
		UserPersonDAO uDao = new UserPersonDAO(); 
		List<Map<String, Object>> users = uDao.selectAll();
		for (Map<String, Object> row : users) {
			String userId = (String) row.get("id");
			if (userId != null && userId.startsWith("WX.R.")) {
				String id = userId.replaceFirst("WX.R.", "UR.F.");
				String dirPath = basePath + "/" + id;
				File dir = new File(dirPath);
				if (!dir.exists()) {
					dir.mkdirs();
				}
				if (!dao.existsRecord("user_repository", id)) {
					String label = "" + row.get("lastName") + " " + row.get("firstName");
					InsertQuery isql = new InsertQuery(dbmd())
					.into("user_repository")
					.addParam("id", ""+id)
					.addParam("parentId", "UR.F.00000000BASEDIR")
					.addParam("userId", ""+userId)
					.addParam("label", ""+label)
					.addParam("image", "")
					.addParam("baseType", "NT.X.UDS000000000URL")
					.addParam("basePath", ""+dirPath)
					.addParam("writerId", "WX.L.0000000000ADMIN")
					.addParam("writeDate", new SQLFunc("NOW"))
					.addParam("editorId", "WX.L.0000000000ADMIN")
					.addParam("editDate", new SQLFunc("NOW"));
					
					qr.update(isql.toSQLString());			
				}
			}
		}
		
		/*
		createModul("LX.G.CONTACT", "LX.M.CONTACT", "Kontakty", "icon-contact", "contact_modul.jsp");
		createModul("LX.G.CALENDAR", "LX.M.CALENDAR", "Kalendář", "icon-calendar", "");
		createModul("LX.G.EMAIL", "LX.M.EMAIL", "Pošta", "icon-email", "");
		createModul("LX.G.REPOSITORY", "LX.M.REPOSITORY", "Disk", "", "");		
		createModul("LX.G.BANK_ACCOUNTS", "LX.M.BANK_ACCOUNTS", "Bankovní účty", "icon-banking", "bank_modul.jsp");
		createModul("LX.G.USER", "LX.M.USER", "Užívatel", "", "user_modul.jsp");
		*/
		/*
		INSERT INTO `gui_modul` (`id`, `modulId`, `label`, `image`, `pageUrl`) VALUES 
		  ('LX.G.CONTACT', 'LX.M.CONTACT', 'Kontakty', 'icon-contact', 'contact_modul.jsp'),
		  ('LX.G.CALENDAR', 'LX.M.CALENDAR', 'Kalendář', 'icon-calendar', ''),
		  ('LX.G.EMAIL', 'LX.M.EMAIL', 'Pošta', 'icon-email', ''),
		  ('LX.G.REPOSITORY', 'LX.M.REPOSITORY', 'Disk', '', ''),
		  ('LX.G.BANK_ACCOUNTS', 'LX.M.BANK_ACCOUNTS', 'Bankovní účty', 'icon-banking', 'bank_modul.jsp'),
		  ('LX.G.USER', 'LX.M.USER', 'Užívatel', '', 'user_modul.jsp'),
		  ('LX.G.BANK_HISTORY', 'LX.M.BANK_HISTORY', 'Bankovní účty - předledy', 'icon-banking', '');
		*/
		/*
		createModulMenu("MN.G.000000000000CRM", "", "CRM", "", 1, "");
		createModulMenu("MN.G.000000000000004", "MN.G.000000000000CRM", "Kontakty", "icon-contact", 1, "LX.M.CONTACT");
		createModulMenu("MN.G.000000000000005", "MN.G.000000000000CRM", "Kalendář", "icon-calendar", 2, "LX.M.CALENDAR");
		createModulMenu("MN.G.000000000000006", "MN.G.000000000000CRM", "Pošta", "icon-email", 3, "LX.M.EMAIL");
		createModulMenu("MN.G.000000000000007", "MN.G.000000000000CRM", "Disk", "", 4, "LX.M.REPOSITORY");		
		createModulMenu("MN.G.00000000000BANK", "", "Banka", "", 2, "");
		createModulMenu("MN.G.000000000000001", "MN.G.00000000000BANK", "Bankovní účty", "icon-banking", 1, "LX.M.BANK_ACCOUNTS");
		//createModulMenu("MN.G.000000000000002", "MN.G.00000000000BANK", "Bankovní účty - předledy", "icon-banking", 2, "LX.M.BANK_HISTORY");
		createModulMenu("MN.G.00000000SETTING", "", "Nastavení", "", 3, "");
		createModulMenu("MN.G.000000000000003", "MN.G.00000000SETTING", "Užívatel", "", 1, "LX.M.USER");
		*/
		/*
		INSERT INTO `gui_modul_menu` (`id`, `parentId`, `label`, `image`, `priority`, `modulId`) VALUES 
		  ('MN.G.000000000000CRM', '', 'CRM', '', 1, ''),
		  ('MN.G.000000000000004', 'MN.G.000000000000CRM', 'Kontakty', 'icon-contact', 1, 'LX.M.CONTACT'),
		  ('MN.G.000000000000005', 'MN.G.000000000000CRM', 'Kalendář', 'icon-calendar', 2, 'LX.M.CALENDAR'),
		  ('MN.G.000000000000006', 'MN.G.000000000000CRM', 'Pošta', 'icon-email', 3, 'LX.M.EMAIL'),
		  ('MN.G.000000000000007', 'MN.G.000000000000CRM', 'Disk', '', 4, 'LX.M.REPOSITORY'),
		  ('MN.G.00000000000BANK', '', 'Banka', '', 2, ''),
		  ('MN.G.000000000000001', 'MN.G.00000000000BANK', 'Bankovní účty', 'icon-banking', 1, 'LX.M.BANK_ACCOUNTS'),
		  ('MN.G.00000000SETTING', '', 'Nastavení', '', 3, ''),
		  ('MN.G.000000000000003', 'MN.G.00000000SETTING', 'Užívatel', '', 1, 'LX.M.USER');
		*/
		
		/*
		INSERT INTO `gui_user_modul_define` (`UID`, `USER_ID`, `MODUL_ID`, `OBJECT_ID`, `PARENT_ID`, `PARAM_KEY`, `PARAM_VALUE`, `CONTENT`) VALUES 
		  ('W_ADMIN_LX.M.CONTACT', 'W_ADMIN', 'LX.M.CONTACT', '', '', '', '', '<USER><MODUL><LIST_VIEW><COLUMN_DEFINE id=\"firstName\" caption=\"Jméno\" width=\"200\" alignment=\"16384\" pattern=\"\"/><COLUMN_DEFINE id=\"lastName\" caption=\"Příjmení\" width=\"200\" alignment=\"16384\" pattern=\"\"/><COLUMN_DEFINE id=\"title\" caption=\"Titul\" width=\"100\" alignment=\"16384\" pattern=\"\"/><COLUMN_DEFINE id=\"writeDate\" caption=\"Datum nar.\" width=\"100\" alignment=\"16348\" pattern=\"\"/><COLUMN_DEFINE id=\"editorId\" caption=\"Opravil\" width=\"100\" alignment=\"16384\" pattern=\"\"/></LIST_VIEW></MODUL></USER>');

		INSERT INTO `user_person` (`id`, `firstName`, `lastName`, `mailId`) VALUES 
		  ('WX.R.AEFBE4JE41ZYBEM', '', '', NULL),
		  ('WX.R.V0IGPTNDYR0MPYN', '', '', NULL),
		  ('WX.R.4YM7IBO7FLRNXBT', NULL, NULL, NULL);
		  
		INSERT INTO `user_login` (`id`, `userName`, `userPassword`, `personId`) VALUES 
		  ('WX.L.5RHPF3REIIU8RTR', 'petr', 'p.Zeli75', 'WX.R.V0IGPTNDYR0MPYN'),
		  ('WX.L.A8AATB5LCJK2VDJ', 'zdenek', 'z.Dyt71', 'WX.R.9MKQOXGCTJECZ1B'),
		  ('WX.L.GZ69V88D4ZWFPF8', 'jarda', 'j.Dyt68', 'WX.R.AEFBE4JE41ZYBEM');

		*/
	}

	public DataSource ds() {
		return DBConnector.instance().getDataSource();
	}

}
